package daoMySQL;

import java.sql.*;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import daoDataFactory.DbObject;
import daoInterface.DaoIDrank;
import domijn.Categorie;
import domijn.Drank;
import domijn.Menu;

public class MySQLDrank extends DbObject implements DaoIDrank {

	private Statement stmt = null;
	private ResultSet rs = null;
	private Menu m;
	private Categorie c;
	private Drank g;
	
	private static MySQLDrank instance;
	
	public static DaoIDrank instance(){
		if (instance == null) {
			 instance = new MySQLDrank();
		}
		return instance;
	}

        @Override
        public void save(Drank g) {
        	String sqlFind =  "SELECT * FROM `drank` WHERE `MenuID` = "+ g.getMenu().getMenuID() + " AND `DrankNummer` = "+ g.getDrNewNumber() +"";
        	try {
        		stmt = openConnection().createStatement();
        		rs = stmt.executeQuery(sqlFind);
        		rs.next();
        		
        		if (rs.getInt(1) == 0){
        		}
        		else{
        			System.out.println("Drank bestaat !");
        			JOptionPane.showMessageDialog(null, " Het drank in menu : " + g.getMenu().getMenuName() + ""
        											  + " met het drank nummer " + g.getDrNewNumber() +" bestaat al" );
        		}
        		} 
        	catch (SQLException sql2) {
        			System.out.println("Drank bestaat nog niet !");
        			try{
        				stmt = openConnection().createStatement();
        				stmt.executeUpdate("INSERT INTO"
        						+ "		`drank`(MenuID,DrankNummer,DrankNaam,DrankPrijs,DrankOmschrijving)"
        						+ "	VALUES("
        						+ "		'"+ g.getMenu().getMenuID()+"',"
        						+ "		'"+ g.getDrNewNumber()+"',"
        						+ "		'"+ g.getDrName()  +"',"
        						+ "		'"+ g.getDrPrice() +"',"
        						+ "		'"+ g.getDrDiscr() +"'"
        						+ ")");
   	    			 	System.out.println("MySQLDrank, public void save, INSERT INTO");	
   	    				}
        			catch(SQLException sql1){
   	    				}
        			try{
        				stmt = openConnection().createStatement();
        				stmt.executeUpdate("INSERT INTO"
        						+ "		`mcc`(MenuID, CategorieID, DrankID)"
        						+ "	VALUES("
        						+ "		        '"+ g.getMenu().getMenuID()+"',"
        						+ "				'"+ g.getCat().getCategorieID()+"',"
        						+ " (SELECT `DrankID` from `drank` "
        						+ " where `drank`.`DrankNummer` = "+g.getDrNewNumber()
        						+ " AND  `drank`.`MenuID` = " + g.getMenu().getMenuID() +"))");
          				}
        			catch(SQLException e){
             			}
        			}
        		}
                
        @Override
        public void update(Drank g) {
		String sqlUpdate = "UPDATE"
				+ "		`drank`"
				+ "	SET"
				+ "		`drank`.`DrankNummer`    ='"+ g.getDrNewNumber()+"',"
				+ "		`drank`.`DrankNaam`  ='"+ g.getDrName() +"',"
				+ "     `drank`.`DrankPrijs` ='"+ g.getDrPrice() +"',"
				+ "		`drank`.`DrankOmschrijving` ='"+ g.getDrDiscr() +"'"
				+ "	WHERE"
				+ "		`drank`.`MenuID` = '" +m.getMenuID()+"'"
				+ " AND" 
				+ "		`drank`.`DrankNummer` ='"+ g.getDrOldNumber()+"'"; 
		try{
			stmt = openConnection().createStatement();
			stmt.executeUpdate(sqlUpdate);
					}
		catch(Exception e1){
			System.out.println(e1);
		}}

        @Override
        public void delete(Drank g) {
        	String sqlDelete = "DELETE FROM"
				+"		`drank`"
				+"	WHERE"
				+"		`drank`.`Drank` = '"+ g.getDrOldNumber()+"'";
		try {
			stmt = openConnection().createStatement();
			stmt.executeUpdate(sqlDelete);
					} 
		catch (Exception e2) {
		}}

		@Override
		public void find(Drank drank) {
			String sqlFind =  " SELECT * "
							+ " FROM `drank` "
							+ " WHERE `MenuID` = "+ m.getMenuID() + ""
							+ " AND `DrankNummer` = "+ g.getDrNewNumber() + "";
			
			try {
        		stmt = openConnection().createStatement();
        		rs = stmt.executeQuery(sqlFind);
        		rs.next(); 
    			g.setDrNewNumber(rs.getInt(3));
    			g.setDrName(rs.getString(4)) ;
    			g.setDrPrice(rs.getString(5));
    			g.setDrDiscr(rs.getString(6));
			}
			catch (SQLException e){
				e.getErrorCode();
			}
		}
		
        @Override
        public ArrayList<Drank> loadall() {
        	ArrayList<Drank>dranken  = new ArrayList<>();
        	String sqlLoadAll = "SELECT"
        		+ "		`menu`.`MenuNaam`,"
				+ "		`categorie`.`CategorieNaam`,"
				+ "  	`drank`.`DrankNummer`,"
				+ "		`drank`.`DrankNaam`, "
				+ "		`drank`.`DrankPrijs`, "
				+ "		`drank`.`DrankOmschrijving`" 
				+ " FROM "
				+ "		`mcc` "
				+ "	INNER JOIN " 
				+ "		`menu` "
				+ " ON "
				+ "		`mcc`.`MenuID` = `menu`.`MenuID`" 
				+ "	INNER JOIN " 
				+ "		`categorie` "
				+ " ON "
				+ "		`mcc`.`CategorieID` = `categorie`.`CategorieID`" 
				+ "	INNER JOIN " 
				+ "		`drank` "
				+ " ON "
				+ "		`mcc`.`DrankID` = `drank`.`DrankID`"
				+ "ORDER BY `menu`.`MenuNaam`, `drank`.`DrankNummer` " ;
		try {
			System.out.println("StringSQL");
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadAll);
			while (rs.next()) {
				m = new Menu(rs.getString(1));
				c = new Categorie(rs.getString(2));
				g = new Drank( m, c, 0, 0, rs.getInt(3), rs.getString(4),rs.getString(5),rs.getString(6));
				dranken.add(g);
			}}catch (SQLException s) {
				System.out.println(s);
			}
		return dranken;
        }

        @Override
        public ArrayList<Drank> loadDBA() {
        	ArrayList<Drank>dranken  = new ArrayList<>();
        	String sqlLoadgba = "SELECT"
				+ "		`categorie`.`CategorieNaam`,"
				+ "  	`drank`.`DrankNummer`,"
				+ "		`drank`.`DrankNaam`, "
				+ "		`drank`.`DrankPrijs`, "
				+ "		`drank`.`DrankOmschrijving`" 
				+ " FROM "
				+ "		`mcc` "
				+ "	INNER JOIN " 
				+ "		`categorie` "
				+ " ON "
				+ "		`mcc`.`CategorieID` = `categorie`.`CategorieID`" 
				+ "	INNER JOIN " 
				+ "		`drank` "
				+ " ON "
				+ "		`mcc`.`DrankID` = `drank`.`DrankID`" 
				+ "	WHERE "
				+ "		`mcc`.`MenuID` = 1"
				+ " ORDER BY `drank`.`DrankNummer`"; 
		try {
			System.out.println("StringSQL");
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadgba);
			while (rs.next()) {
				c = new Categorie(rs.getString(1));
				g = new Drank(m, c, 0, 0,rs.getInt(2), rs.getString(3),rs.getString(4),rs.getString(5));
				dranken.add(g);
				System.out.println();
			}}catch (SQLException s) {
				System.out.println(s);
			}
		return dranken;
	}

        @Override
        public ArrayList<Drank> loadDBR() {
        	ArrayList<Drank>dranken  = new ArrayList<>();
        	String sqlLoadgba = "SELECT"
    				+ "		`categorie`.`CategorieNaam`,"
    				+ "  	`drank`.`DrankNummer`,"
    				+ "		`drank`.`DrankNaam`, "
    				+ "		`drank`.`DrankPrijs`, "
    				+ "		`drank`.`DrankOmschrijving`" 
    				+ " FROM "
    				+ "		`mcc` "
    				+ "	INNER JOIN " 
    				+ "		`categorie` "
    				+ " ON "
    				+ "		`mcc`.`CategorieID` = `categorie`.`CategorieID`" 
    				+ "	INNER JOIN " 
    				+ "		`drank` "
    				+ " ON "
    				+ "		`mcc`.`DrankID` = `drank`.`DrankID`" 
    				+ "	WHERE "
    				+ "		`mcc`.`MenuID` = 2"
    				+ " ORDER BY `drank`.`DrankNummer`"; 
		try {
			System.out.println("StringSQL");
			stmt = openConnection().createStatement();
			rs = stmt.executeQuery(sqlLoadgba);
			while (rs.next()) {
				c = new Categorie(rs.getString(1));
				g = new Drank(m, c, 0, 0, rs.getInt(2), rs.getString(3),rs.getString(4),rs.getString(5));
				dranken.add(g);
				System.out.println();
			}}catch (SQLException s) {
				System.out.println(s);
			}
		return dranken;
	}

  
}